package com.hs.chd.dataprocess;

 

import java.io.File;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import com.hs.auth.model.HsAccount;
import com.hs.chd.model.SchdChnpaper;
import com.hs.core.db.DBMgr;
import com.hs.core.imp.ExcelImportUtil;
 

public class DataProcess {

	/**
	 * @param args
	 */
	public static void main(String[] args) {
		// TODO Auto-generated method stub
		//importxwxx();
		//importxwxx2();
//       DBMgr dbo = DBMgr.getDbUtilInst();
//       List datalist = dbo.queryList("select to_char(YWXX) as ywxx,YW_ID from \"c_yflf\" where YWXX is not null", null);
//       for(int i =0;i<datalist.size();i++){
//    	   Map data = (Map)datalist.get(i);
//    	   String ywid =  data.get("YW_ID")+"";
//    	   String ywxx =  (String)data.get("ywxx");
//    	   if(ywid == null || "null".equals(ywid) || null == ywxx)
//    		   continue;
//    	   String[] dataarr = ywxx.split("\\)");
//    	   for(int j=0;j<dataarr.length;j++){
//    		   String temp = dataarr[j];
//    		   if(null != temp){
//    			  // System.out.println(temp+":"  );
//    			   temp = temp.substring(1,temp.length());
//    			   String[] herbarr = temp.split(";");
//    			   if(herbarr.length > 0){
//    				   dbo.update("insert into SCHD_TCM_YWXX(ID,YW_ID,YWMC) VALUES(SEQ_IMPORT_ID.nextval,?,?)", new Object[]{ywid,herbarr[0]});
//    				   if(herbarr[0].length()>10)
//        			     System.out.println(ywid+":" + herbarr[0]);
//    			   }
//    				    
//
//    		   } 
//    	   }
//       }
		 DBMgr dbo = DBMgr.getDbUtilInst();
		 ExcelImportUtil inst = ExcelImportUtil.getInstance(new File("e:\\data.xls"));
		 HSSFWorkbook workbook = null;  
 		 try {
				workbook = inst.readFile();
				int total = workbook.getNumberOfSheets(); //获取sheet个数      
		        int start = 0;
		        int end = 0 ;   
		        //获取sheet个数
				for(int i = 0 ; i < 1 ; i ++){  
		            HSSFSheet sheet = workbook.getSheetAt(i);// 按索引获取sheet引用   
		            start = sheet.getFirstRowNum();  //sheet起始行索引      
		            end = sheet.getLastRowNum();//sheet起结束行索引     
		            int counter = 1;
		            HSSFRow row = null ; 
		            HSSFCell id = null ; 
		        	HSSFCell mc = null ;  
		        	HSSFCell ywid = null ;
		        	  while(counter <= end){      
			            	SchdChnpaper maga = null;
			                 row =  sheet.getRow(counter);//获取第N行表格数据     
			                 if(row != null){
			                	  counter ++ ; 
			                	  id = row.getCell(0);           
			                	  mc = row.getCell(1);   
			                	  ywid = row.getCell(2); 
			                	  System.out.println("id:" +  getCell(id).trim());
			                	  System.out.println("mc:" +  getCell(mc).trim());
			                	  System.out.println("ywid:" +  getCell(ywid).trim());
			                	  System.out.println("counter:" +  counter);
			                	  dbo.update("insert into SCHD_TCM_CLINIC_C_LCZL(id,BZZMC,yw_id) values(?,?,?)", new Object[]{getCell(id).trim(),getCell(mc).trim(),getCell(ywid).trim()});
			                	  
			                 }
		        	  }
		        	
 				}
				}catch(Exception e){
					e.printStackTrace();
				}
		
	}
	public static String getCell(HSSFCell cell) {
		if (cell == null)
			return "";
		switch (cell.getCellType()) {
		case HSSFCell.CELL_TYPE_NUMERIC:
			return (long) cell.getNumericCellValue() + "";
		case HSSFCell.CELL_TYPE_STRING:
			return cell.getStringCellValue();
		case HSSFCell.CELL_TYPE_FORMULA:
			return cell.getCellFormula();
		case HSSFCell.CELL_TYPE_BLANK:
			return "";
		case HSSFCell.CELL_TYPE_BOOLEAN:
			return cell.getBooleanCellValue() + "";
		case HSSFCell.CELL_TYPE_ERROR:
			return cell.getErrorCellValue() + "";
		default:
			return "";
		}
	}
	static void importxwxx(){
		DBMgr dbo = DBMgr.getDbUtilInst();
	       List datalist = dbo.queryList("select to_char(XWXX) as xwxx,YW_ID,id from \"c_ztlf\" where XWXX is not null and XWXX NOT LIKE '%);%'", null);
	       for(int i =0;i<datalist.size();i++){
	    	   Map data = (Map)datalist.get(i);
	    	   String ywid =  data.get("YW_ID")+"";
	    	   String ywxx =  (String)data.get("xwxx");
	    	   String id =   data.get("id") + "";
	    	   
	    	   if(ywid == null || "null".equals(ywid) || null == ywxx)
	    		   continue;
	    	   if(!ywxx.endsWith(")")){
	    		   System.out.println(ywid+":" + id);
	    		   System.out.println(ywxx );
	    	   }
	    	   String[] dataarr = ywxx.split("\\)");
	    	   for(int j=0;j<dataarr.length;j++){
	    		   String temp = dataarr[j];
	    		   if(null != temp){
	    			   System.out.println(temp  );
	    			   temp = temp.substring(1,temp.length());
	    			   String[] herbarr = temp.split(";");
	    			   if(herbarr.length > 0){
	    				  dbo.update("insert into SCHD_TCM_XWXX(ID,YW_ID,XWMC) VALUES(SEQ_IMPORT_ID.nextval,?,?)", new Object[]{ywid,herbarr[0]});
	    				   //if(herbarr[0].length()>10)
	        			     System.out.println(ywid+":" + herbarr[0]);
	    			   }
	    				    

	    		   } 
	    	   }
	       }
	}
	static void importxwxx2(){
		DBMgr dbo = DBMgr.getDbUtilInst();
	       List datalist = dbo.queryList("select to_char(XWXX) as xwxx,YW_ID,id from \"c_ztlf\" where XWXX is not null and XWXX   LIKE '%);%'", null);
	       for(int i =0;i<datalist.size();i++){
	    	   Map data = (Map)datalist.get(i);
	    	   String ywid =  data.get("YW_ID")+"";
	    	   String ywxx =  (String)data.get("xwxx");
	    	   String id =   data.get("id") + "";
	    	   
	    	   if(ywid == null || "null".equals(ywid) || null == ywxx)
	    		   continue;
	    	   
	    	   String[] dataarr = ywxx.split("\\$");
	    	   for(int j=0;j<dataarr.length;j++){
	    		   String temp = dataarr[j];
	    		   if(null != temp){
	    			  // System.out.println(ywxx);
	    			  // System.out.println(temp);
	    			    
	    			   String[] herbarr = temp.split(";");
	    			   if(herbarr.length > 0){
	    				  dbo.update("insert into SCHD_TCM_XWXX(ID,YW_ID,XWMC) VALUES(SEQ_IMPORT_ID.nextval,?,?)", new Object[]{ywid,herbarr[0]});
	    				   //if(herbarr[0].length()>10)
	        			     System.out.println(ywid+":" + herbarr[0]);
	    			   }
	    				    

	    		   } 
	    	   }
	       }
	}


}
